import pandas as pd
from functools import reduce
from xlrd.biffh import XLRDError
from pandas.errors import ParserError

pd.set_option('display.max_columns', 20)


# GUI 相关 ==============================================================================================================
TITLE = '报表指标整理工具'
WINDOW_SIZE = '600x500'

# 报表内容相关 =========================================================================================================
REPORT_FORM_FOLDER = '../report_forms'
FILE_SUFFIXES = ('.xls', '.xlsx')
HEADER_START_RANGE, HEADER_RANGE = range(9), range(1, 5)
INVALID_COLUMN = 'Unnamed'
PERIOD_INDICATOR1, PERIOD_INDICATOR2 = ('日期', '月份')
CATEGORY, BUSI_TYPE, FORM_NAME, INDEX_SPLIT, INDEX_NAME, DETAILED_SUM, INDEX_DIM, UPDATE_PERIOD = '报表类别', '业务类别', '报表名称', '指标拆分', '字段名称', '统计明细', '字段维度', '更新周期'
OUTPUT_TEMPLATE = {col: [] for col in (CATEGORY, BUSI_TYPE, FORM_NAME, INDEX_SPLIT, INDEX_NAME, DETAILED_SUM, INDEX_DIM, UPDATE_PERIOD)}
PROVINCE_LABEL = '省'
MONTH_PERIOD_LABEL = '统计月份'
MONTH = '月'
index_split = ' || '

# 系统相关 ==============================================================================================================
OUTPUT_FOLDER = '../outputs'
OUTPUT_FILE_REPORT_FORM_SUM = '{}/{}'.format('../outputs', '指标汇总.xlsx')
OUTPUT_FILE_FIELDS_INTERGRATION = '{}/{}'.format('../outputs', '标签及指标.xlsx')
NOTATED_FOLDER = '../notated_files'
PROCESSING_PROMPT = '正在处理文件：{} ...'
PROCESSED_PROMPT = '表格：{} 处理完成！\n'
NOT_PROCESSED_PROMPT = '未能处理表格：{}\n'

# ======================================================================================================================


def _check_str(tup) -> bool:
    '''
    判断tuple里的元素是否均为string
    :param tup: 关联列tuple
    :return: 布尔值
    '''
    return reduce(lambda x, y: x and y, list(map(lambda x: type(x) is str, tup)))


def _extract_update_period(columns) -> str:
    '''
    从表头上方筛选条件提取表格更新日期
    :param columns: 表头列的列表
    :return: '月'或'日'
    '''
    if sum(map(lambda x: x == PERIOD_INDICATOR1 or x == PERIOD_INDICATOR2, [])):
        pass


def _check_header_valid(columns):
    '''
    检查表头是否有效
    :param columns: 表头列的列表
    :return: 布尔值
    '''
    try:
        # 复合表头，检查列名唯一性和数据格式是否为字符串
        if type(columns[0]) is tuple and reduce(lambda x, y: x and y, [_check_str(tup) for tup in columns]):
            all_cols = [tuple(tup[-i] for i in range(1, len(tup)+1)) for tup in columns]
            distinct_cols = set([tuple(tup[-1].split('.')[0] if i == 1 else tup[-i] for i in range(1, len(tup)+1)) for tup in columns])
            valid = len(all_cols) == len(distinct_cols)
        # 单行表头，检查列名唯一性及是否为pandas自动补齐
        elif type(columns[0]) is str:
            no_replicative_columns = len(columns) == len(set([col.split('.')[0] for col in columns]))
            no_invalid_columns = reduce(lambda x, y: x and y, list(map(lambda x: INVALID_COLUMN not in x, columns)))
            valid = no_replicative_columns and no_invalid_columns
        else:
            valid = False

        return valid

    except:
        return False


def _reader_helper(file_path: str, sheet_name: str) -> pd.DataFrame:
    '''
    遍历表行以找到表头的开始及表头的宽度
    :param file_path: Excel文件路径
    :param sheet_name: 页名
    :return: 数据框
    '''
    for i in HEADER_START_RANGE:
        for j in HEADER_RANGE:
            try:
                df = pd.read_excel(io=file_path, sheet_name=sheet_name, header=list(range(i, i + j)))
                update_period = _extract_update_period(df.columns)
                valid = _check_header_valid(df.columns)
                if valid: return df
            except ParserError:
                continue


def read_form(file_path: str, sheet_name: str=None) -> {str: pd.DataFrame}:
    '''
    读取非空的Excel表格，返回形如{页名: 数据框}的字典
    :param file_path: 文件路径
    :param sheet_name: 指定的数据页名，若为None则读取全部
    :return: 字典
    '''
    try:
        if sheet_name == None:
            df_dict = pd.read_excel(io=file_path, sheet_name=sheet_name)
            for sheet_name, sheet_df in df_dict.items():
                df_dict[sheet_name] = _reader_helper(file_path, sheet_name)

        else:
            df_dict = {}
            df_dict[sheet_name] = _reader_helper(file_path, sheet_name)

        return df_dict

    except XLRDError as err:
        print(err)
    except Exception as err:
        raise(err)


def check_suffix(file_name: str, suffixes: tuple) -> bool:
    '''
    检查文件名后缀是否在给定后缀列表中
    :param file_name: 文件名
    :param suffixes: 后缀列表
    :return: 布尔值
    '''
    return reduce(lambda x, y: x or y, list(map(file_name.endswith, suffixes)))




